package com.example.util;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by douyx on 2017/11/16.
 */
public class ExportExcelUtil {

    /**
     * 双表头Excel导出
     *
     * @param tableName   表名
     * @param tablelist   数据列名
     * @param tablelistEc 表头用于对应映射
     * @param itemList    数据
     * @return
     * @throws Exception
     */
    public static Map<String, Object> writeExcel(String tableName, List<String> tablelist, List<String> tablelistEc,List<HashMap<String, Object>> itemList, String path) throws Exception {

        Map<String, Object> result = new HashMap<>();

        if (tablelist.size() != tablelistEc.size()) {
            throw new RuntimeException("列名个数不一致");
        }
        if (itemList != null && itemList.size() > 0) {

            FileOutputStream fileOutStream = null;
//            int rowNum = itemList.size(); //行数
            int colNum = tablelist.size(); //列数

            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            String dateString = sdf.format(new Date());
            if (path.endsWith(File.separator)) {
                path = path + dateString + File.separator;
            } else {
                path = path + File.separator + dateString + File.separator;
            }

            File file1 = new File(path);
            if (!file1.exists()) {
                file1.mkdirs();
            }

            // 重新生成文件名，防止中文乱码
            String fileName = UUID.randomUUID() + ".xlsx";
            path = path + fileName;

            result.put("fileUrl", dateString + File.separator + fileName);
            result.put("fileName", tableName + ".xlsx");

            fileOutStream = new FileOutputStream(path);
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet(tableName);

            //3.自动调整列的宽度
            for (int i = 0; i < colNum; i++) {
                int colWidth = sheet.getColumnWidth(i) * 2;
                sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
            }
            // 4.合并单元格以及设置宽度
            mergeRegionAndSetWidth(sheet, colNum);
            // 5.绘制表单
            // 5.1 样式

            Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
            // 设置标题
            CellStyle style = wb.createCellStyle();
            style.setAlignment(CellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            Font titleFont = wb.createFont();
            titleFont.setFontName("Arial");
            titleFont.setFontHeightInPoints((short) 16);
            titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            style.setFont(titleFont);
            styles.put("title", style);


            style = wb.createCellStyle();
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            style.setBorderRight(CellStyle.BORDER_THIN);
            style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderLeft(CellStyle.BORDER_THIN);
            style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderTop(CellStyle.BORDER_THIN);
            style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderBottom(CellStyle.BORDER_THIN);
            style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            Font dataFont = wb.createFont();
            dataFont.setFontName("Arial");
            dataFont.setFontHeightInPoints((short) 10);
            style.setFont(dataFont);
            styles.put("data", style);

            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
            style.setAlignment(CellStyle.ALIGN_LEFT);
            styles.put("data1", style);

            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
            style.setAlignment(CellStyle.ALIGN_CENTER);
            styles.put("data2", style);

            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
            style.setAlignment(CellStyle.ALIGN_RIGHT);
            styles.put("data3", style);

            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
            // style.setWrapText(true);
            style.setAlignment(CellStyle.ALIGN_CENTER);
            style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
            Font headerFont = wb.createFont();
            headerFont.setFontName("Arial");
            headerFont.setFontHeightInPoints((short) 10);
            headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            headerFont.setColor(IndexedColors.WHITE.getIndex());
            style.setFont(headerFont);
            styles.put("header", style);

            //第一行，表名
            String reportName = tableName;
            XSSFRow row0 = sheet.createRow(0);
            row0.setHeightInPoints(20);
            for (int i = 0; i < colNum; i++) {
                XSSFCell cell0i = row0.createCell(i);
                cell0i.setCellValue(reportName);
                cell0i.setCellStyle(styles.get("title"));
            }
            //第二行,添加列名
            XSSFRow row1 = sheet.createRow(1);
            XSSFCell cell0 = row1.createCell(0);
            cell0.setCellValue("序号");
            cell0.setCellStyle(styles.get("header"));
            int num = 0;

            // 循环创建表头
            for (String name : tablelist) {
                XSSFCell celli = row1.createCell(num + 1);
                celli.setCellValue(name);
                celli.setCellStyle(styles.get("header"));
                num++;
            }
            //循环创建表格,将查询信息放入表格
            for (int i = 0; i < itemList.size(); i++) {
                XSSFRow row = sheet.createRow(i + 2);
                row.setHeightInPoints(22f);

                Map<String, Object> map1 = itemList.get(i);

                XSSFCell celli = row.createCell(0);
                celli.setCellValue(Integer.toString((i + 1)));//创建表格序号
                celli.setCellStyle(styles.get("data3"));
                int j = 0;
                for (String key : tablelistEc) {
                    Object value = map1.get(key);

                    XSSFCell cell = row.createCell(j + 1);
                    cell.setCellStyle(styles.get("data3"));
                    if (value instanceof Date ||
                            value instanceof Calendar ||
                            value instanceof String ||
                            value instanceof Float ||
                            value instanceof Double ||
                            value instanceof Integer ||
                            value instanceof BigDecimal) {
                        if (value instanceof Date) {
                            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            String format = simpleDateFormat.format((Date) value);
                            cell.setCellValue(format);
                        }
                        if (value instanceof String) {
                            cell.setCellValue((String) value);
                        }
                        if (value instanceof Float || value instanceof Double) {
                            cell.setCellValue((Double) value);
                        }
                        if (value instanceof Integer) {
                            cell.setCellValue((Integer) value);
                        }
                        if (value instanceof Calendar) {
                            cell.setCellValue((Calendar) value);
                        }
                        if (value instanceof BigDecimal) {
                            BigDecimal value1 = (BigDecimal) value;
                            String value2 = value1.toString();
                            cell.setCellValue(value2);
                        }
                    } else {
                        cell.setCellValue("数据类型异常");
                    }
                    j++;
                }
            }
            //关闭
            wb.write(fileOutStream);
            fileOutStream.flush();
            fileOutStream.close();
        }
        return result;
    }



    /**
     * 双表头Excel导出
     *
     * @param tableName   表名
     * @param tablelist   数据列名
     * @param tablelistEc 表头用于对应映射
     * @param itemList    数据
     * @return
     * @throws Exception
     */
    public static Map<String, Object> writeMapExcel(String tableName, List<String> tablelist, List<String> tablelistEc,List<Map<String, Object>> itemList, String path) throws Exception {

        Map<String, Object> result = new HashMap<>();

        if (tablelist.size() != tablelistEc.size()) {
            throw new RuntimeException("列名个数不一致");
        }
        if (itemList != null && itemList.size() > 0) {

            FileOutputStream fileOutStream = null;
//            int rowNum = itemList.size(); //行数
            int colNum = tablelist.size(); //列数

            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            String dateString = sdf.format(new Date());
            if (path.endsWith(File.separator)) {
                path = path + dateString + File.separator;
            } else {
                path = path + File.separator + dateString + File.separator;
            }

            File file1 = new File(path);
            if (!file1.exists()) {
                file1.mkdirs();
            }

            // 重新生成文件名，防止中文乱码
            String fileName = UUID.randomUUID() + ".xlsx";
            path = path + fileName;

            result.put("fileUrl", dateString + File.separator + fileName);
            result.put("fileName", tableName + ".xlsx");

            fileOutStream = new FileOutputStream(path);
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet(tableName);

            //3.自动调整列的宽度
            for (int i = 0; i < colNum; i++) {
                int colWidth = sheet.getColumnWidth(i) * 2;
                sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
            }
            // 4.合并单元格以及设置宽度
            mergeRegionAndSetWidth(sheet, colNum);
            // 5.绘制表单
            // 5.1 样式

            Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
            // 设置标题
            CellStyle style = wb.createCellStyle();
            style.setAlignment(CellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            Font titleFont = wb.createFont();
            titleFont.setFontName("Arial");
            titleFont.setFontHeightInPoints((short) 16);
            titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            style.setFont(titleFont);
            styles.put("title", style);


            style = wb.createCellStyle();
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            style.setBorderRight(CellStyle.BORDER_THIN);
            style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderLeft(CellStyle.BORDER_THIN);
            style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderTop(CellStyle.BORDER_THIN);
            style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderBottom(CellStyle.BORDER_THIN);
            style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            Font dataFont = wb.createFont();
            dataFont.setFontName("Arial");
            dataFont.setFontHeightInPoints((short) 10);
            style.setFont(dataFont);
            styles.put("data", style);

            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
            style.setAlignment(CellStyle.ALIGN_LEFT);
            styles.put("data1", style);

            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
            style.setAlignment(CellStyle.ALIGN_CENTER);
            styles.put("data2", style);

            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
            style.setAlignment(CellStyle.ALIGN_RIGHT);
            styles.put("data3", style);

            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
            // style.setWrapText(true);
            style.setAlignment(CellStyle.ALIGN_CENTER);
            style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
            Font headerFont = wb.createFont();
            headerFont.setFontName("Arial");
            headerFont.setFontHeightInPoints((short) 10);
            headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            headerFont.setColor(IndexedColors.WHITE.getIndex());
            style.setFont(headerFont);
            styles.put("header", style);

            //第一行，表名
            String reportName = tableName;
            XSSFRow row0 = sheet.createRow(0);
            row0.setHeightInPoints(20);
            for (int i = 0; i < colNum; i++) {
                XSSFCell cell0i = row0.createCell(i);
                cell0i.setCellValue(reportName);
                cell0i.setCellStyle(styles.get("title"));
            }
            //第二行,添加列名
            XSSFRow row1 = sheet.createRow(1);
            XSSFCell cell0 = row1.createCell(0);
            cell0.setCellValue("序号");
            cell0.setCellStyle(styles.get("header"));
            int num = 0;

            // 循环创建表头
            for (String name : tablelist) {
                XSSFCell celli = row1.createCell(num + 1);
                celli.setCellValue(name);
                celli.setCellStyle(styles.get("header"));
                num++;
            }
            //循环创建表格,将查询信息放入表格
            for (int i = 0; i < itemList.size(); i++) {
                XSSFRow row = sheet.createRow(i + 2);
                row.setHeightInPoints(22f);

                Map<String, Object> map1 = itemList.get(i);

                XSSFCell celli = row.createCell(0);
                celli.setCellValue(Integer.toString((i + 1)));//创建表格序号
                celli.setCellStyle(styles.get("data3"));
                int j = 0;
                for (String key : tablelistEc) {
                    Object value = map1.get(key);

                    XSSFCell cell = row.createCell(j + 1);
                    cell.setCellStyle(styles.get("data3"));
                    if (value instanceof Date ||
                            value instanceof Calendar ||
                            value instanceof String ||
                            value instanceof Float ||
                            value instanceof Double ||
                            value instanceof Integer ||
                            value instanceof BigDecimal) {
                        if (value instanceof Date) {
                            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            String format = simpleDateFormat.format((Date) value);
                            cell.setCellValue(format);
                        }
                        if (value instanceof String) {
                            cell.setCellValue((String) value);
                        }
                        if (value instanceof Float || value instanceof Double) {
                            cell.setCellValue((Double) value);
                        }
                        if (value instanceof Integer) {
                            cell.setCellValue((Integer) value);
                        }
                        if (value instanceof Calendar) {
                            cell.setCellValue((Calendar) value);
                        }
                        if (value instanceof BigDecimal) {
                            BigDecimal value1 = (BigDecimal) value;
                            String value2 = value1.toString();
                            cell.setCellValue(value2);
                        }
                    } else {
                        cell.setCellValue("数据类型异常");
                    }
                    j++;
                }
            }
            //关闭
            wb.write(fileOutStream);
            fileOutStream.flush();
            fileOutStream.close();
        }
        return result;
    }
    //合并单元格
    private static void mergeRegionAndSetWidth(XSSFSheet sheet, int colNum) {
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colNum));
    }


}
